Data file considerations
When working with data files, one-to-many links can occur when you link tables in a single report or when you add a subreport to your report.
Linking data files
When retrieving data from linked data files in one-to-many situations, the program uses the following process:
In this formula, the part before the "and" operator contains range selection criteria for the Region field. The region must fall alphabetically between "CA" and "IL." The program passes this kind of condition down to either the database DLL (for PC data) or the server (for SQL data). See Record Selection.
The second half of the selection formula, however, requires processing that must be done in the Report Engine. It uses a built-in function to manipulate and evaluate a field value and it cannot be done in the database DLL or the server. The program does not pass this condition to the database DLL.
- If there is an index on Table A, and the range limit selection condition is based on the indexed field ({customer.REGION} in this example), the program goes directly to the record it is seeking in Table A (the first CA record) and reads it.
Note: The lookup table in a link (Table B) must always be indexed; otherwise, you will not be able to link the tables.
- if there is no index on Table A, or if there is an index but the range limit selection condition is not based on the indexed field, the program reads the first record it finds.
Subreports and data files
If your primary report is based on Table A, the subreport is based on Table B, and the records are linked, your primary considerations are as follows:
- the number of subreports that are run by the program is determined by the index and the selection formula situation in the primary report
- if Table A is indexed, and if the primary report has a selection formula that passes down range limit conditions for the indexed field, the program runs two subreports
- if Table A is not indexed, or if Table A is indexed but the selection formula does not pass down range limit conditions for the indexed field, the program runs 26 subreports
- the number of records read for each subreport is determined by the index situation on Table B
- if you have an index on Table B, the program will read only the matching records (100) when it runs a subreport
- if you do not have an index on Table B, the program will always read every record in Table B (2600) when it runs a subreport.